package strukturaZamestnanciNepritomnost;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.Set;
import java.util.TreeMap;
import java.util.logging.Level;
import java.util.logging.Logger;
import strukturaSviatky.Sviatky;

public class Zamestnanci {

    //private variables
    private boolean connected = false;
    private Connection con = null;
    private String url = "jdbc:mysql://kempelen.ii.fmph.uniba.sk:3306/";
    private String db = "vratnica?useUnicode=true&characterEncoding=UTF-8";
    private String driver = "com.mysql.jdbc.Driver";
    private String user = "vratnica";
    private String pass = "MonikaVeronika";
    private TreeMap<Integer, Zamestnanec> zamestnanci; //Integer = ID zamestnanca, Zamestnanec - udaje zamestnanca  
    private TreeMap<Integer, ArrayList<Nepritomnost>> nepritomnost; //Integer = ID zamestnanca, ArrayList - zoznam nepritomnosti prislusneho zamestnanca

    public Zamestnanci() throws SQLException, ParseException {
        zamestnanci = new TreeMap<>();
        nepritomnost = new TreeMap<>();


        if (connect()) {
            loadZamestnanci();
            Set<Integer> keySet = zamestnanci.keySet();
            for (Integer key : keySet) {
                nepritomnost.put(key, new ArrayList<Nepritomnost>());
            }
            loadNepritomnost();
        } else {
            System.out.println("Nepodarilo sa pripojit k databaze.");
        }
    }

    public int addZamestnanec(Zamestnanec zamestnanec) throws SQLException {
        //pridanie noveho zamestnanca do databazy
        Statement st = con.createStatement();
        st.executeUpdate("INSERT INTO zamestnanci_table VALUES (null, '" + zamestnanec.getMeno() + "', '" + zamestnanec.getPriezvisko() + "', " + zamestnanec.getVahovanie() + ", " + zamestnanec.getPocetDniDovolenky() + ", " + zamestnanec.getTypZamestnanca() + ", 1)");

        ResultSet res = st.executeQuery("SELECT ID FROM zamestnanci_table ORDER BY ID ASC");

        int result = 0;
        while (res.next()) {
            result = res.getInt("ID");
        }
        zamestnanci.put(new Integer(result), zamestnanec);
        nepritomnost.put(result, new ArrayList<Nepritomnost>());
        return result;
    }

    public boolean editZamestnanec(int ID, Zamestnanec zamestnanec) throws SQLException {
        //nastavenie novych udajov zamestnanca pre zvolene ID zamestnanca
        if (zamestnanci.containsKey(new Integer(ID))) {
            Statement st = con.createStatement();
            String updateData = "UPDATE zamestnanci_table SET meno = '" + zamestnanec.getMeno() + "', priezvisko = '" + zamestnanec.getPriezvisko() + "', vahovanie = " + zamestnanec.getVahovanie() + ", pocet_dni_dovolenky = " + zamestnanec.getPocetDniDovolenky() + ", typ_zamestnanca = " + zamestnanec.getTypZamestnanca() + " WHERE ID = " + ID;
            st.executeUpdate(updateData);

            zamestnanci.remove(ID);
            zamestnanci.put(ID, zamestnanec);
            return true;
        }
        return false;
    }

    public boolean removeZamestnanec(int ID) throws SQLException {
        //odstranenie zamestnanca z databazy, jeho vsetkych zaznamov o nepritomnosti a taktiez aj jeho vsetkych zaznamov o vztahoch s ostatnymi zamestnancami
        if (zamestnanci.containsKey(new Integer(ID))) {
            //vymazanie vztahov s kazdym zamestnancom
            Set<Integer> keySet = zamestnanci.keySet();
            for (Integer key : keySet) {
                removeVztah(ID, key);
            }

            //vymazanie zamestnanca
            Statement st = con.createStatement();
            String deleteData = "UPDATE zamestnanci_table SET active = 0 WHERE ID = " + ID;
            st.executeUpdate(deleteData);

            zamestnanci.remove(ID);

            //vymazanie nepritomnosti
            removeNepritomnost(ID);
            nepritomnost.remove(ID);



            return true;
        }
        return false;
    }

    public TreeMap<Integer, Zamestnanec> getZamestnanciActive() {
        //vrati strukturu aktivnych zamestnancov - pouzivat iba na ziskanie dat, nie na upravu dat!
        TreeMap<Integer, Zamestnanec> result = new TreeMap<>();
        Set<Integer> keySet = zamestnanci.keySet();
        for (Integer key : keySet) {
            if (zamestnanci.get(key).isActive()) {
                result.put(key, zamestnanci.get(key));
            }
        }

        return result;
    }

    public TreeMap<Integer, Zamestnanec> getZamestnanciAll() {
        //vrati strukturu vsetkych zamestnancov - pouzivat iba na ziskanie dat, nie na upravu dat!
        return zamestnanci;
    }

    public Zamestnanec getZamestnanec(int ID) {
        //vrati zamestnanca podla ID zamestnanca - pouzivat iba na ziskanie dat, nie na upravu dat!
        return zamestnanci.get(ID);
    }

    public int getIDZam(Zamestnanec z) {
        //vrati ID zamestnanca podla objektu Zamestnanec - pouzivat iba na ziskanie dat, nie na upravu dat!
        Set<Integer> keySet = zamestnanci.keySet();
        for (Integer key : keySet) {
            if (zamestnanci.get(key).equals(z)) {
                return key;
            }
        }

        return 0;
    }

    public boolean addNepritomnost(int ID_zam, Nepritomnost n) throws SQLException {
        //pridanie zaznamu o nepritomnosti zvoleneho zamestnanca
        if (zamestnanci.containsKey(new Integer(ID_zam))) {
            Statement st = con.createStatement();
            st.executeUpdate("INSERT INTO nepritomnost_table VALUES (null, " + ID_zam + ", '" + n.getDatumSQL() + "', '" + n.getTyp() + "', " + n.getDlzka() + ")");

            nepritomnost.get(ID_zam).add(n);
            return true;
        }
        return false;
    }

    public boolean removeNepritomnost(int ID_zam, String datum) throws SQLException, ParseException {
        //odstranenie zaznamu o nepritomnosti zvoleneho zamestnanaca pre dany den
        SimpleDateFormat sdf1 = new SimpleDateFormat("dd.MM.yyyy");
        Calendar c = new GregorianCalendar();
        c.setTime(sdf1.parse(datum));
        SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd");

        if (zamestnanci.containsKey(new Integer(ID_zam))) {
            Statement st = con.createStatement();
            String deleteData = "DELETE FROM nepritomnost_table WHERE ID_zam = " + ID_zam + " AND datum = '" + sdf2.format(c.getTime()) + "'";
            st.executeUpdate(deleteData);

            for (int i = 0; i < nepritomnost.get(ID_zam).size(); i++) {
                if (nepritomnost.get(ID_zam).get(i).getDatum().equals(datum)) {
                    nepritomnost.get(ID_zam).remove(i);
                }
            }
            return true;
        }
        return false;
    }

    public boolean removeNepritomnost(int ID_zam) throws SQLException {
        //odstranenie vsetkych zaznamov o nepritomnosti zamestnanca

        Statement st = con.createStatement();
        String deleteData = "DELETE FROM nepritomnost_table WHERE ID_zam = " + ID_zam;
        st.executeUpdate(deleteData);

        nepritomnost.get(ID_zam).clear();
        return true;

    }

    public ArrayList<Nepritomnost> getNepritomnostZamestnanca(int ID) {
        //vrati zoznam nepritomnosti zvoleneho zamestnanca - pouzivat iba na ziskanie dat, nie na upravu dat!
        return nepritomnost.get(ID);
    }

    public TreeMap<Integer, ArrayList<Nepritomnost>> getNepritomnostZamestnancov() {
        //vrati zoznam nepritomnosti vsetkych zamestnancov - pouzivat iba na ziskanie dat, nie na upravu dat!
        return nepritomnost;
    }

    public boolean isConnected() {
        //vrati true ak je pripojene do databazy
        return connected;
    }

    public boolean addVztah(int ID_zam1, int ID_zam2, int rating) throws SQLException {
        //pridanie noveho vztahu
        Statement st = con.createStatement();
        st.executeUpdate("INSERT INTO vztahy_table VALUES (null, " + ID_zam1 + ", " + ID_zam2 + ", " + rating + ")");

        zamestnanci.get(ID_zam1).addVztah(ID_zam2, rating);
        zamestnanci.get(ID_zam2).addVztah(ID_zam1, rating);

        return true;
    }

    public boolean editVztah(int ID_zam1, int ID_zam2, int rating) throws SQLException {
        //upravenie existujuceho vztahu
        removeVztah(ID_zam1, ID_zam2);
        if (rating != 0) {
            addVztah(ID_zam1, ID_zam2, rating);
        }
        return true;
    }

    public boolean removeVztah(int ID_zam1, int ID_zam2) throws SQLException {
        //odtranenie vztahu
        Statement st = con.createStatement();
        st.executeUpdate("DELETE FROM vztahy_table WHERE (ID_zam1 = " + ID_zam1 + " AND ID_zam2 = " + ID_zam2 + ") OR (ID_zam2 = " + ID_zam1 + " AND ID_zam1 = " + ID_zam2 + ")");

        zamestnanci.get(ID_zam1).removeVztah(ID_zam2);
        zamestnanci.get(ID_zam2).removeVztah(ID_zam1);

        return true;
    }

    //private methods
    private boolean connect() {
        try {
            Class.forName(driver).newInstance();
            con = DriverManager.getConnection(url + db, user, pass);
            Statement st = con.createStatement();
            st.executeQuery("SET NAMES utf8 COLLATE utf8_slovak_ci");
            st = con.createStatement();
            st.executeQuery("SET CHARACTER SET utf8");
            connected = true;
            return true;
        } catch (Exception ex) {
            Logger.getLogger(Sviatky.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    private boolean loadZamestnanci() throws SQLException, ParseException {
        Statement st = con.createStatement();

        //nacitanie zamestnancov
        ResultSet res = st.executeQuery("SELECT * FROM zamestnanci_table");

        while (res.next()) {
            zamestnanci.put(new Integer(res.getInt("ID")), new Zamestnanec(res.getString("meno"), res.getString("priezvisko"), res.getDouble("vahovanie"), res.getDouble("pocet_dni_dovolenky"), res.getInt("typ_zamestnanca")));
            if (res.getInt("active") == 0) {
                zamestnanci.get(res.getInt("ID")).setInactive();
            }
        }

        //nacitanie vztahov medzi zamestnancami
        res = st.executeQuery("SELECT * FROM vztahy_table");

        while (res.next()) {
            zamestnanci.get(res.getInt("ID_zam1")).addVztah(res.getInt("ID_zam2"), res.getInt("vztah"));
            zamestnanci.get(res.getInt("ID_zam2")).addVztah(res.getInt("ID_zam1"), res.getInt("vztah"));
        }

        return true;
    }

    private boolean loadNepritomnost() throws SQLException, ParseException {
        Statement st = con.createStatement();

        ResultSet res = st.executeQuery("SELECT * FROM nepritomnost_table ORDER BY ID_zam, datum");

        while (res.next()) {
            Calendar c = new GregorianCalendar();
            c.setTime(new SimpleDateFormat("yyyy-MM-dd").parse(res.getString("datum")));
            nepritomnost.get(res.getInt("ID_zam")).add(new Nepritomnost(c, res.getString("typ_nepritomnosti"), res.getDouble("dlzka_nepritomnosti")));
        }
        return true;
    }
}
